package Server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Queue;

public class PhoneServerDB {

	private int connCounter = 0;
	
	private static PhoneServerDB DB = new PhoneServerDB();
	
	private PhoneServerDB() {}
	
	public static PhoneServerDB getPhoneServerDB() {
		return DB;
	}
	
	private synchronized Connection getConn() {
		
		Connection conn = null;
		
		try {
			//System.out.println("Connecting to server database...");
			
			Class.forName("org.sqlite.JDBC");
			String home = System.getProperty("user.home");
			conn = DriverManager.getConnection("jdbc:sqlite:" + home + "/db/VoIP");
		
		} catch (Exception e) {
			System.out.println("Error loading database.");
			System.exit(-1);
		}
		
		return conn;
		
	}
	
	private synchronized void closeConn(Connection conn) {
		
		try {
			conn.close();
		} catch (SQLException e) {
			System.out.println("Error closing database connection.");
		}
	}
	
	public synchronized int getCallLoad() {
		
		int callLoad = 3;
		
		Connection conn = getConn();
		
		try {
			Statement stat = conn.createStatement();
			
			String q = "select value from System where name = 'CallLoad';";
			ResultSet rs = stat.executeQuery(q);
			
			if (rs.next()) {
				callLoad = Integer.parseInt(rs.getString("value"));  
			} else {
				System.out.println("Failed to load CallLoad setting from database.");
			}
			rs.close();
			
		} catch (SQLException e) {
			System.out.println("Error while trying to retrieve CallLoad from database.");
		}
		
		closeConn(conn);
		
		return callLoad;
		
	}
	
	public synchronized int getBillingPeriod() {
		
		int period = 1;
		
		Connection conn = getConn();
		
		try {
			Statement stat = conn.createStatement();
			
			String q = "select value from System where name = 'BillingPeriod';";
			ResultSet rs = stat.executeQuery(q);
			
			if (rs.next()) {
				period = Integer.parseInt(rs.getString("value"));  
			} else {
				System.out.println("Failed to load BillingPeriod setting from database.");
			}
			rs.close();
			
		} catch (SQLException e) {
			System.out.println("Error while trying to retrieve BillingPeriod from database.");
		}
		
		closeConn(conn);
		
		return period;
		
	}
	
	public synchronized void getPhoneInfo(PhoneServerThread phone) {
		
		Connection conn = getConn();
		
		try {
			System.out.println("Getting phone info for phone: " + phone.getPhoneName());
			
			Statement stat = conn.createStatement();
		
			String q = "select * from PhoneAccount where ip = '" + phone.getPhoneName() + "';";
			ResultSet rs = stat.executeQuery(q);
			
			if (rs.next()) {
				
				phone.setExtension(rs.getInt("extension"));
				//phoneHash.put(phone.getExtension(), phone);
				
				int p = rs.getInt("permissions");
				String accountStatus = rs.getString("accountStatus");
				if (p == 0) {
					phone.setPhoneStatus(PhoneServerThread.Status.DEACTIVATED);
				} else if (p == 1) {
					phone.setPhoneStatus(PhoneServerThread.Status.ACTIVATED);
				} else if (p == 2) {
					phone.setPhoneStatus(PhoneServerThread.Status.CANNOT_CALL);
				} else {
					phone.setPhoneStatus(PhoneServerThread.Status.CANNOT_RECEIVE);
				}
				
				if (accountStatus.equals("Suspended") || accountStatus.equals("Cancelled"))
					phone.setPhoneStatus(PhoneServerThread.Status.DEACTIVATED);
				
				System.out.println("Extension and permissions retrieved: " + phone.getExtension() + ":" + p);
				
			} else {
				System.out.println("Could not find information for phone " + phone.getPhoneName() + " in the database.");
				//phone.disconnectPhone();
			}
			rs.close();
		
		} catch (Exception e) {
			System.out.println("Error loading phone settings from database.");
			System.out.println(e);
			//System.exit(-1);
		}
		
		closeConn(conn);		
		
	}
	
	private class Discount {
		String startDay;
		String endDay;
		Date startTime;
		Date endTime;
		double rate;
	}
	
	private synchronized int dayStringToInt(String day) {
		if(day.equalsIgnoreCase("sunday")) {
			return 0;
		} else if(day.equalsIgnoreCase("monday")) {
			return 1;
		} else if(day.equalsIgnoreCase("tuesday")) {
			return 2;
		} else if(day.equalsIgnoreCase("wednesday")) {
			return 3;
		} else if(day.equalsIgnoreCase("thursday")) {
			return 4;
		} else if(day.equalsIgnoreCase("friday")) {
			return 5;
		} else if(day.equalsIgnoreCase("saturday")) {
			return 6;
		} else {
			return -1;
		}
	}
	
	@SuppressWarnings("deprecation")	
	private synchronized int compareTime(Date timeOne, Date timeTwo) {
		int intTimeOne = timeOne.getHours() * 3600 + timeOne.getMinutes() * 60 + timeOne.getSeconds();
		int intTimeTwo = timeTwo.getHours() * 3600 + timeTwo.getMinutes() * 60 + timeTwo.getSeconds();
		
		return (intTimeOne - intTimeTwo / Math.abs(intTimeOne - intTimeTwo));
	}
	
	@SuppressWarnings("deprecation")
	public synchronized double getDiscountRate(int extension, Date time) {
		double rate = -1.0;
		
		Connection conn = getConn();
		
		try {
			System.out.println("Getting Base Rate for " + extension);
			
			Statement stat = conn.createStatement();
			
			String q = "select b.id, b.base_rate" +
				" from BillingPlan b, BillingToPhone c, PhoneAccount p" +
				" where p.extension = '" + extension + "' and" +
					" p.id = c.phone_id and b.id = c.plan_id;";
			ResultSet rs = stat.executeQuery(q);
			
			if(rs.next()) {
				int billingPlan = rs.getInt("id");
				rate = rs.getDouble("base_rate");
				System.out.println("Base rate set to " + rate);
				
				rs.close();
				
				System.out.println("Getting Discount Plans for " + extension);
				
				q = "select d.startDay, d.startTime, d.endDay, d.endTime, d.rate " +
						"from DiscountPlan d, BillingToDiscount b " +
						"where b.plan_id = " + billingPlan +" and " +
							"b.disc_id = d.id;";
				rs = stat.executeQuery(q);
				
				Queue<Discount> discs = new LinkedList<Discount>();
				Discount current;
				while(rs.next()) {
					current = new Discount();
					current.startDay = rs.getString("startDay");
					current.startTime = rs.getTime("startTime");
					current.endDay = rs.getString("endDay");
					current.endTime = rs.getTime("endTime");
					current.rate = rs.getDouble("rate");
					
					discs.add(current);
				}
				
				rs.close();
				
				System.out.println("Finding Discount Rate");
				
				int day = time.getDay();
				
				while((current = discs.poll()) != null) {
					int startDay = dayStringToInt(current.startDay);
					int endDay = dayStringToInt(current.endDay);
					
					if(startDay < endDay) {
						if(startDay > day && day > endDay) continue;
					} else if(startDay > endDay) {
						if(startDay < day || day < endDay) continue;
					} else if(day != startDay) continue;
					
					if(startDay == day) {
						if(compareTime(current.startTime, time) > 0) continue;
					}
					
					if(endDay == day) {
						if(compareTime(current.endTime, time) < 0) continue;
					}
					
					rate *= (1-current.rate);
					System.out.println("Discount rate set to " + rate);
					break;
				}
				
			} else {
				rs.close();
				System.out.println("Could not find Billing Plan for that extension");
			}
			
		} catch (Exception e) {
			System.out.println("Error getting Discount Plan");
			e.printStackTrace();
		}
		
		closeConn(conn);
		
		return rate;
	}
	
	public synchronized void insertCallRecord (Date start_time, Date end_time, double rate, int callerext, int calleeext)
	{
		if (start_time != end_time && start_time != null)
		{
			Connection conn = getConn();
			
			try {
			
				Statement stat = conn.createStatement();
			
				String q = "select * from PhoneAccount where extension = '" + callerext + "';";
				ResultSet rs = stat.executeQuery(q);
				
				int id = 0;
	
				if(rs.next())
				{
					id = rs.getInt("id");
				} else {
					System.err.println("PhoneAccount not in database!");
				}
				
				rs.close();
				
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				
				String start = df.format(start_time);
				String end = df.format(end_time);
				
				PreparedStatement ps;
	
			    double duration = (end_time.getTime() - start_time.getTime()) / 6000;
			    duration = Math.ceil(duration);
			    double amount = rate * duration;
	
			    ps = conn.prepareStatement("insert into CallRecord (start_time, end_time, caller_id, to_ext, amount) values (datetime(?), datetime(?), ?, ?, ?)");
			    ps.setString(1, start);
			    ps.setString(2, end);
			    ps.setInt(3, id);
			    ps.setInt(4, calleeext);
			    ps.setDouble(5, amount);  
			    
			    ps.execute();
		
			} catch (Exception e) {
				System.out.println("Error loading phone settings from database.");
				//System.exit(-1);
			}
			
			closeConn(conn);
		}
	}
	
	public synchronized Date getLastBillDate(int id) {
		
		Date latest = new Date(0);
		
		Connection conn = getConn();
		
		try {
			
			Statement stat = conn.createStatement();
			String q = "select * from PhoneBill where phone_id = " + id + ";";
			
			ResultSet rs = stat.executeQuery(q);
			
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Date d;
			
			while (rs.next()){
				d = df.parse(rs.getString("end_time"));
				if (d.after(latest))
					latest = d;
			}

			rs.close();
			
		} catch (Exception e) {
			System.out.println("Error getting last bill date from database.");
			e.printStackTrace();
		}
		
		closeConn(conn);
		
		return latest;
		
	}
	
	
	public synchronized double getCallRecordAmount(Date start, Date end, int id) {
		
		double amount = 0;
		
		Connection conn = getConn();
		
		try {
			
			Statement stat = conn.createStatement();
			String q = "select * from CallRecord where caller_id = " + id + ";";
			
			ResultSet rs = stat.executeQuery(q);
			
			Date callEnd;
			double callAmount;
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			while(rs.next()) {
				
				callEnd = df.parse(rs.getString("end_time"));				
				callAmount = rs.getDouble("amount");
				
				if (callEnd.after(start) && (callEnd.before(end) || callEnd.equals(end)))
					amount += callAmount;
			}
			rs.close();
			
		} catch (Exception e) {
			System.out.println("Error calculating bill amount from CallRecord.");
			e.printStackTrace();
		}
		
		closeConn(conn);
		
		return amount;
	}
	
	public synchronized List<Integer> getPhoneIds() {
	
		List<Integer> result = new ArrayList<Integer>();
	
		Connection conn = getConn();
		
		try {
			
			Statement stat = conn.createStatement();
			String q = "select id from PhoneAccount;";
			ResultSet rs = stat.executeQuery(q);
			
			while(rs.next()) {
				result.add(new Integer(rs.getInt("id")));
			}
			
			rs.close();
			
		} catch (Exception e) {
			System.out.println("Error getting list of phone ids.");
			e.printStackTrace();
		}
		
		closeConn(conn);
		
		return result;
		
	}
	
	public synchronized void insertBill(int phone_id, Date start_time, Date end_time, double amount) {
		
		Connection conn = getConn();
		
		try {
			
			Statement stat = conn.createStatement();
			String q = "select * from PhoneAccount where id = " + phone_id + ";";
			
			ResultSet rs = stat.executeQuery(q);
		
			double balance = 0;
			if (rs.next()) {
				balance = rs.getDouble("balance");
			} else {
				System.out.println("No entry for PhoneAccount " + phone_id + " in database.");
			}
			rs.close();
			
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			q = "insert into PhoneBill(phone_id, start_time, end_time, balance, total)" +
			"values(?, datetime(?), datetime(?), ?, ?);";
			PreparedStatement ps = conn.prepareStatement(q);
			
			ps.setInt(1, phone_id);
			ps.setString(2, df.format(start_time));
			ps.setString(3, df.format(end_time));
			ps.setDouble(4, balance);
			ps.setDouble(5, amount);
			
			ps.executeUpdate();	
			
			q = "update PhoneAccount set balance = ? where id = ?;";
			ps = conn.prepareStatement(q);
			ps.setDouble(1, amount + balance);
			ps.setInt(2, phone_id);
			ps.executeUpdate();
			
				
		} catch (Exception e) {
			System.out.println("Error storing bill for phone_id " + phone_id + "in database.");
		}
		closeConn(conn);
		
	}
	
	public synchronized void updatePhoneStatus(String ip, String status) {
	
		Connection conn = getConn();
		
		try {
			String q = "update PhoneAccount set phoneStatus = ? where ip = ?";
		
			PreparedStatement ps = conn.prepareStatement(q);
			ps.setString(1, status);
			ps.setString(2, ip);
			ps.executeUpdate();
	
		} catch (Exception e) {
			System.out.println("Error storing phone status in database.");
			e.printStackTrace();
		}
		
		closeConn(conn);
		
	}
	
	public synchronized void insertAdminMessage (String subject, String body, Date date, String viewed) 
	{
		Connection conn = getConn();
		
		try {
			System.out.println("Storing message for admin: " + subject);
		
			String q = "insert into Messages(subject, body, date, viewed) " +
					"values(?, ?, ?, ?);";
		
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String d = df.format(date);
			
			PreparedStatement ps = conn.prepareStatement(q);
			ps.setString(1, subject);
			ps.setString(2, body);
			ps.setString(3, d);
			ps.setString(4, viewed);
			ps.executeUpdate();
	
		} catch (Exception e) {
			System.out.println("Error storing admin message in database.");
			e.printStackTrace();
			//System.exit(-1);
		}
		
		closeConn(conn);
	}	
}
